package com.shujia.gma.controller;

import com.shujia.gma.bean.SkuIndex;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;

@RestController
public class SkuController {


    private static Connection con;

    static {
        //1、加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        //建立连接
        try {
            con = DriverManager.getConnection("jdbc:mysql://node1:3306/gma_ads?useUnicode=true&characterEncoding=UTF-8", "root", "123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    @GetMapping("/getSkuIndex")
    public ArrayList<SkuIndex> getSkuIndex(Integer id, String day) {

        ArrayList<SkuIndex> skuIndices = new ArrayList<>();


        try {
            PreparedStatement stat = con.prepareStatement("select * from ads_sku_index where sku_id=? and dt=?");

            stat.setLong(1, id);
            stat.setString(2, day);

            ResultSet resultSet = stat.executeQuery();


            /**
             *   sku_id  bigint(20),
             *   weight decimal(10,2),
             *   tm_name varchar(200)  ,
             *   price decimal(10,2),
             *   spu_id varchar(200) ,
             *   category3_name varchar(200)  ,
             *   category2_name varchar(200) ,
             *   category1_name varchar(200) ,
             *   order_count  bigint(20),
             *   order_amount decimal(10,2),
             *   sku_count  bigint(20),
             *   dt varchar(200) ,
             *
             */

            while (resultSet.next()) {
                BigDecimal weight = resultSet.getBigDecimal("weight");
                String tm_name = resultSet.getString("tm_name");
                BigDecimal price = resultSet.getBigDecimal("price");
                String spu_id = resultSet.getString("spu_id");
                String category3_name = resultSet.getString("category3_name");
                String category2_name = resultSet.getString("category2_name");
                String category1_name = resultSet.getString("category1_name");
                long order_count = resultSet.getLong("order_count");
                BigDecimal order_amount = resultSet.getBigDecimal("order_amount");
                long sku_count = resultSet.getLong("sku_count");

                SkuIndex skuIndex = new SkuIndex(id, weight, tm_name, price, spu_id, category3_name, category2_name, category1_name, order_count, order_amount, sku_count, day);

                skuIndices.add(skuIndex);
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }

        return skuIndices;
    }

}
